﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Net;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using System.Reflection;


namespace 药品信息管理
{
    class getSqlConnection
    {
        //string str_ConnectionString = "server=.;DataBase='YXFYBJY';User ID='HIS';Password='MANAGER'";
        string str_ConnectionString = "server=222.16.86.84;DataBase='YKXT';User ID='sa';Password='123'";
        SqlConnection G_Con;
        public SqlConnection GetCon()
        {
            G_Con = new SqlConnection(str_ConnectionString);
            G_Con.Open();
            return G_Con;
        }

    }

    class db
    {
        public DataTable sqldt(string sql)
        {
            string str = "server=222.16.86.84;DataBase='YKXT';User ID='sa';Password='123'";
            //string str = "server=.;DataBase='YXFYBJY';User ID='HIS';Password='MANAGER'";
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            con.Close();
            return dt;
        }
        public string sqlstr(string sql)
        {
            string str = "server=222.16.86.84;DataBase='YKXT';User ID='sa';Password='123'";
            //string str = "server=.;DataBase='YXFYBJY';User ID='HIS';Password='MANAGER'";
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand com = new SqlCommand(sql, con);
            string abc = "";
            try { abc = com.ExecuteScalar().ToString(); }
            catch { }
            con.Close();
            return abc;
        }
        public int sqlcmd(string sql)
        {
            string str = "server=222.16.86.84;DataBase='YKXT';User ID='sa';Password='123'";
            //string str = "server=.;DataBase='YXFYBJY';User ID='HIS';Password='MANAGER'";
            SqlConnection con = new SqlConnection(str);
            con.Open();
            SqlCommand com = new SqlCommand(sql, con);
            int abc = com.ExecuteNonQuery();
            con.Close();
            return abc;
        }

        public bool Excutesql(string sql)
        {
            SqlConnection conn;
            string strConnection = @"server=222.16.86.84;database=YKXT;uid=sa;pwd=123";
            int Rows = 0;
            conn = new SqlConnection(strConnection);
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand command = new SqlCommand(sql, conn);
                Rows = command.ExecuteNonQuery();
                if (Rows == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (SqlException ex)
            {
                string error = ex.ToString();
                return false;
            }
            conn.Close();
        }


        public int datetoint(string x)
        {
            int aa = (DateTime.Parse(x) - DateTime.Parse("2011.04.26")).Days;
            return (aa + 40659);
        }
        public string inttodate(string x)
        {
            db db1 = new db();
            return (DateTime.Parse(db1.sqlstr("select convert(datetime," + x + "-2)")).ToString("yyyy.MM.dd"));
        }

        public void DataToExcel(DataGridView m_DataView)
        {
            SaveFileDialog kk = new SaveFileDialog(); 
            kk.Title = "保存EXECL文件"; 
            kk.Filter = "EXECL文件(*.xls) |*.xls |所有文件(*.*) |*.*"; 
           kk.FilterIndex = 1;
           if (kk.ShowDialog() == DialogResult.OK) 
            { 
                string FileName = kk.FileName ;
                if (File.Exists(FileName))
                    File.Delete(FileName);
                FileStream objFileStream; 
                StreamWriter objStreamWriter; 
               string strLine = ""; 
                objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write); 
                objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
                for (int i = 0; i  < m_DataView.Columns.Count; i++) 
                { 
                   if (m_DataView.Columns[i].Visible == true) 
                    { 
                        strLine = strLine + m_DataView.Columns[i].HeaderText.ToString() + Convert.ToChar(9); 
                    } 
                } 
               objStreamWriter.WriteLine(strLine); 
                strLine = ""; 

                for (int i = 0; i  < m_DataView.Rows.Count; i++) 
                { 
                   if (m_DataView.Columns[0].Visible == true) 
                   { 
                        if (m_DataView.Rows[i].Cells[0].Value == null) 
                            strLine = strLine + " " + Convert.ToChar(9); 
                        else 
                            strLine = strLine + m_DataView.Rows[i].Cells[0].Value.ToString() + Convert.ToChar(9); 
                   } 
                    for (int j = 1; j  < m_DataView.Columns.Count; j++) 
                    { 
                        if (m_DataView.Columns[j].Visible == true) 
                        { 
                            if (m_DataView.Rows[i].Cells[j].Value == null) 
                               strLine = strLine + " " + Convert.ToChar(9); 
                            else 
                            { 
                                string rowstr = ""; 
                               rowstr = m_DataView.Rows[i].Cells[j].Value.ToString(); 
                                if (rowstr.IndexOf("\r\n") >  0) 
                                    rowstr = rowstr.Replace("\r\n", " "); 
                                if (rowstr.IndexOf("\t") >  0) 
                                    rowstr = rowstr.Replace("\t", " "); 
                                strLine = strLine + rowstr + Convert.ToChar(9); 
                            } 
                        } 
                    } 
                    objStreamWriter.WriteLine(strLine); 
                    strLine = ""; 
                } 
                objStreamWriter.Close(); 
                objFileStream.Close();
              MessageBox.Show("保存EXCEL成功"); 
           }
        }

    }   

}
